<?php

/**
 * Created by PhpStorm.
  * User: longli
 * VX: isa1589518286
 * Date: 2020/07/04
 * Time: 18:33
 * @link http://www.lmterp.cn
 */
namespace app\common\library;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception;

class FileExcel
{
    /**
     * 文件路径
     * @var string
     */
    protected $file;

    /**
     * 错误信息
     * @var array
     */
    protected $error = [];

    /**
     * 指定跳过多少行
     * @var int
     */
    protected $line = 0;

    public function __construct($file)
    {
        $this->file = $file;
    }

    /**
     * 读取 excel 格式文件
     * @param array $header 自定义关联数组下标，如果不传则使用第一行当数组下标
     * @param mixed $callback 回调函数
     * example 回调函数：$excel->read(function($item, $key, &$info)
     * {
     *    if(empty($item))
     *    {
     *       $info[] = '自定义验证异常';
     *       return false;
     *    }
     *    return $item;
     * });
     * @return array|bool
     * @date 2020/07/04
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @author longli
     */
    public function read($header = [], $callback = null)
    {
        // 判断文件是否存在
        if(!is_file($this->file))
        {
            $this->error[] = '文件不存在';
            return false;
        }
        $inputFileType = strtolower(IOFactory::identify($this->file));
        if(!in_array($inputFileType, ['xlsx', 'xls', 'csv']))
        {
            $this->error[] = '文件格式有误，请检查';
            return false;
        }
        /*
        $inputFileType = IOFactory::identify($this->file); // 获取文件类型  csv, Xlsx, Xls
        $excelReader = IOFactory::createReader($inputFileType);
        $phpexcel = $excelReader->load($this->file);
        */
        $excelReader = IOFactory::createReaderForFile($this->file);
        // csv文件读取设置
        if ($inputFileType == 'csv')
        {
            $excelReader->setInputEncoding('GBK');
            $excelReader->setDelimiter(',');
        }
        $phpExcel = $excelReader->load($this->file);
        $activeSheet = $phpExcel->getActiveSheet();
        $sheet = $activeSheet->toArray();
        // 验证参数类型
        if(empty($header) || is_callable($header))
        {
            if(is_callable($header)) $callback = $header;
            $header = Tools::trim(array_filter(array_shift($sheet)));
        }
        $sheet = Tools::trim($sheet);
        $ret = [];
        $count = count($header);
        $isCallable = is_callable($callback);
        foreach($sheet as $key => $item)
        {
            if($key < $this->getLine()) continue;
            $item = array_combine($header, array_splice($item, 0, $count));
            if($isCallable) $item = $callback($item, $key, $this->error);
            if($item === false) return false;
            if(empty($item)) continue;
            $ret[] = $item;
        }
        return $ret;
    }

    /**
     * 生成 excel 文件
     * @param array $header 表头
     * @param array $data 内容
     * @param string $filename 文件名
     * @param string $docType 文件类型，默认 xlsx, 可选（xlsx, xls）
     * @date 2020/07/04
     * @return string
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws Exception
     * @author longli
     */
    public function write(array $header = [], array $data = [], $filename = '', $docType = '')
    {
        $docType = ucfirst($docType);
        if($docType != 'Xls') $docType = 'Xlsx';
        $header = Tools::trim($header);
        $data = Tools::trim($data);
        $count = count($header);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $wh = $this->getWriteHeader($header);
        for($i = 0; $i < $count; $i++)
        {
            // chr(65) 等于大写 A
            $sheet->setCellValue("{$wh[$i]}1", $header[$i]);
            $spreadsheet->getActiveSheet()->getColumnDimension($wh[$i])->setWidth(20); //固定列宽
        }
        foreach($data as $key => $item)
        {
            $item = array_values($item);
            for ($i = 0; $i < $count; $i++)
            {
                $value = isset($item[$i]) ? $item[$i] : '';
                $sheet->setCellValue($wh[$i] . ($key + 2), $value);
            }
        }
        $writer = IOFactory::createWriter($spreadsheet, $docType);
        $path = rtrim($this->file) . "/";
        if(!is_dir($path)) mkdir($path, 0777, true);
        $filePath = $path . (!empty($filename) ? $filename : date('YmdHis') . "." . strtolower($docType));
        $writer->save($filePath);
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        return $filePath;
    }

    /**
     * 获取写入的单元格
     * @param array $header 表头
     * @return array 写入的单元格
     * @date 2020/08/29
     * @author longli
     */
    public function getWriteHeader($header = [])
    {
        $p = 0;
        $prefix = "";
        $ret = [];
        $count = count($header) + 65;
        for($i = 65; $i < $count; $i++)
        {
            $temp = $i - 65;
            $suffix = chr($i);
            if($temp > 25)
            {
                if($temp % 26 == 0)
                {
                    $prefix = chr(65 + $p);
                    $p++;
                }
                $suffix = chr($i - 26 * $p);
            }
             $ret[] = "{$prefix}{$suffix}";
        }
        return $ret;
    }

    /**
     * @return string
     */
    public function getFile()
    {
        return $this->file;
    }

    /**
     * @param string $file
     */
    public function setPath($file)
    {
        $this->file = $file;
    }

    /**
     * @return array
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     * @return int
     */
    public function getLine()
    {
        return $this->line;
    }

    /**
     * @param int $line
     */
    public function setLine(int $line)
    {
        $this->line = $line;
    }
}